﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-05-04
修改:
修改时间: 
功能: 企划唯一码报表
 ******************************************************/

using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.library.Helper;
using george.Web.Helper;
using com.nm.library;

namespace PlanAPI
{
    [Description("企划唯一码报表")]
    public class PLOnlyCodeHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();
        
        /// Writer：梁家健
        /// Create Date：2017-05-04
        [MustLogin]
        [Description("企划唯一码报表列表")]
        [ReturnType(typeof(PaperData<PLOnlyCodeModel.ListModel>))]
        public ApiResult GetPLOnlyCodeList([ParamDescription("唯一码或店铺")] string CodeOrShop = "",
            [ParamDescription("年份")] string Yearv = "", [ParamDescription("月份")] string Monthv = "",
            [ParamDescription("系列")] string SeriesID = "", [ParamDescription("商品层")] string MaterialProperty = "",
            [ParamDescription("品类")] string ModelID = "", [ParamDescription("小品类")] string ItemID = "",
            [ParamDescription("商品区域")] string ParentAreaID = "", [ParamDescription("省份")] string ProID = "",
            [ParamDescription("城市")] string CityID = "", [ParamDescription("店铺Keys")] string Keys = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(CodeOrShop)) return Error("唯一码或店铺条件未选！");
                if (string.IsNullOrEmpty(Yearv) || string.IsNullOrEmpty(Monthv)) return Error("请选择年月！");
                var codeOrShop = Convert.ToInt64(CodeOrShop);
                var count = ReturnPLOnlyCodeCount(codeOrShop, Yearv, Monthv, SeriesID, MaterialProperty, ModelID,
                    ItemID, ParentAreaID, ProID, CityID, Keys);
                var result = PaperData<PLOnlyCodeModel.ListModel>.Create(count, Size, Page);
                var dt =
                    ReturnPLOnlyCodeList(codeOrShop, Yearv, Monthv, SeriesID, MaterialProperty, ModelID,
                        ItemID, ParentAreaID, ProID, CityID, Keys, Page, Size);
                var list = ModelConvertHelper<PLOnlyCodeModel.ListModel>.ConvertToModel(dt);
                if (codeOrShop == 1L)
                {
                    var group = list.GroupBy(d => new {d.Yearv, d.Monthv, d.ParentAreaID, d.ShopID});
                    result.Items = group.Select(d => new PLOnlyCodeModel.ListModel
                    {
                        Yearv = d.Key.Yearv,
                        Monthv = d.Key.Monthv,
                        ParentAreaID = d.Key.ParentAreaID,
                        ParentAreaName = d.First().ParentAreaName,
                        ShopID = d.Key.ShopID,
                        ShopName = d.First().ShopName,
                        count = d.Count(),
                        list =
                            d.GroupBy(
                                r =>
                                    r.Yearv + "_" + r.Monthv + "_" + r.ParentAreaID + "_" + r.ShopID + "_" +
                                    r.KindID + "_" + r.SeriesID + "_" + r.MaterialProperty + "_" + r.ModelID + "_" +
                                    r.ItemID + "_" + r.Weekv).ToList(),
                    }).ToList();
                }
                else
                {
                    var group = list.GroupBy(d => new {d.Yearv, d.Monthv});
                    result.Items = group.Select(d => new PLOnlyCodeModel.ListModel
                    {
                        Yearv = d.Key.Yearv,
                        Monthv = d.Key.Monthv,
                        list =
                            d.GroupBy(
                                r =>
                                    r.Yearv + "_" + r.Monthv + "_" + r.KindID + "_" + r.SeriesID + "_" +
                                    r.MaterialProperty + "_" + r.ModelID + "_" + r.ItemID + "_" + r.Weekv).ToList(),
                    }).ToList();
                }
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回企划唯一码报表列表
        /// </summary>
        /// <param name="CodeOrShop"></param>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="ItemID"></param>
        /// <param name="ParentAreaID"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="Keys"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-05-04
        public DataTable ReturnPLOnlyCodeList(long CodeOrShop, string Yearv, string Monthv, string SeriesID,
            string MaterialProperty, string ModelID, string ItemID, string ParentAreaID, string ProID, string CityID,
            string Keys, int Page, int Size)
        {
            try
            {
                var con1 = @"";
                var con2 = @"";
                var con3 = @"";
                var con4 = @"";
                var con5 = @"";
                if (CodeOrShop == 1L)
                {
                    con1 = @" inner join ndw.ndw_global_dictionary dict on code.ParentAreaID=dict.dictName 
     and  code.ExtrSource = dict.ExtrSource and code.CompanyID=dict.CompanyID 
                        inner join dw.dw_Shop shop on code.ShopID=shop.ShopID 
    and  code.ExtrSource = shop.ExtrSource and code.CompanyID=shop.CompanyID 
                        inner join ods.ods_erp_Bas_Area area on shop.AreaID=area.AreaID 
                            and  shop.ExtrSource = area.ExtrSource 
";
                    con2 = @" ,code.ParentAreaID,dict.dictValue as ParentAreaName,code.ShopID,code.ShopName ";
                    con3 = @" ,code.ParentAreaID,dict.dictValue,code.ShopID,code.ShopName ";
                    con4 = @" ,table2.ParentAreaID,table2.ParentAreaName,table2.ShopID,table2.ShopName ";
                    con5 = @" and table2.ParentAreaID=code.ParentAreaID
                        and table2.ShopID=code.ShopID";
                    SeriesID = "";
                    MaterialProperty = "";
                    ModelID = "";
                    ItemID = "";
                }
                else
                {
                    ParentAreaID = "";
                    ProID = "";
                    CityID = "";
                    Keys = "";
                }
                var sql = $@"SELECT  table2.* ,
        code.Weekv ,
        code.OnlyCode ,
        sum(code.DeepQty) as DeepQty,
        ISNULL(code.ChooseFlag, 0) AS ChooseFlag
FROM    ( SELECT    *
          FROM      ( SELECT    code.Yearv ,
                                code.Monthv ,
                                code.KindID ,
                                kind.KindName ,
                                code.SeriesID ,
                                series.SeriesName ,
                                code.MaterialProperty ,
                                code.ModelID ,
                                model.ModelName ,
                                code.ItemID ,
                                item.ItemName ,
                                ROW_NUMBER() OVER ( ORDER BY code.Yearv DESC, code.Monthv DESC {con3}, code.KindID, code.SeriesID, code.MaterialProperty, code.ModelID, code.ItemID ) AS Row
                                {con2}
                            FROM     ngoods.ngoods_PL_OnlyCode code
        INNER JOIN nods.nods_erp_SD_Mat_Kind kind ON code.KindID = kind.KindID  and  code.ExtrSource = kind.ExtrSource 
        INNER JOIN nods.nods_erp_SD_Mat_Series series ON code.SeriesID = series.SeriesID  and  code.ExtrSource = series.ExtrSource 
        INNER JOIN nods.nods_erp_SD_Mat_Model model ON code.ModelID = model.ModelID  and  code.ExtrSource = model.ExtrSource 
        INNER JOIN nods.nods_erp_SD_Mat_Item item ON code.ItemID = item.ItemID  and  code.ExtrSource = item.ExtrSource 
                                {con1}
                      WHERE     1=1
      and   code.ExtrSource = '{UserInfo.UserExtrSource}' and code.CompanyID='{UserInfo.UserCompanyId}'
                                {(string.IsNullOrEmpty(Yearv) ? "" : $@" and code.Yearv = '{Yearv}' ")}
                                {(string.IsNullOrEmpty(Monthv) ? "" : $@" and code.Monthv = '{Monthv}' ")}
                                {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and code.SeriesID = '{SeriesID}' ")}
                                {(string.IsNullOrEmpty(MaterialProperty)
                    ? ""
                    : $@" and code.MaterialProperty = '{MaterialProperty}' ")}
                                {(string.IsNullOrEmpty(ModelID) ? "" : $@" and code.ModelID = '{ModelID}' ")}
                                {(string.IsNullOrEmpty(ItemID) ? "" : $@" and code.ItemID = '{ItemID}' ")}
                                {(string.IsNullOrEmpty(ParentAreaID)
                    ? ""
                    : $@" and code.ParentAreaID = '{ParentAreaID}' ")}
                                {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID = '{ProID}' ")}
                                {(string.IsNullOrEmpty(CityID) ? "" : $@" and shop.AreaID = '{CityID}' ")}
                                {(string.IsNullOrEmpty(Keys)
                    ? ""
                    : $@" AND ( code.ShopName LIKE '%{Keys}%' OR shop.ShopCode LIKE '%{Keys}%' ) ")}
                      GROUP BY  code.Yearv ,
                                code.Monthv ,
                                code.KindID ,
                                kind.KindName ,
                                code.SeriesID ,
                                series.SeriesName ,
                                code.MaterialProperty ,
                                code.ModelID ,
                                model.ModelName ,
                                code.ItemID ,
                                item.ItemName
                                {con3}
                    ) table1
          WHERE     Row BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size}
        ) table2
        INNER JOIN ( SELECT code.*
                     FROM   ngoods.ngoods_PL_OnlyCode code
                            {con1}
                     WHERE  1=1
and code.ExtrSource = '{UserInfo.UserExtrSource}' and code.CompanyID='{UserInfo.UserCompanyId}' 
                            {(string.IsNullOrEmpty(Yearv) ? "" : $@" and code.Yearv = '{Yearv}' ")}
                            {(string.IsNullOrEmpty(Monthv) ? "" : $@" and code.Monthv = '{Monthv}' ")}
                            {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and code.SeriesID = '{SeriesID}' ")}
                            {(string.IsNullOrEmpty(MaterialProperty)
                    ? ""
                    : $@" and code.MaterialProperty = '{MaterialProperty}' ")}
                            {(string.IsNullOrEmpty(ModelID) ? "" : $@" and code.ModelID = '{ModelID}' ")}
                            {(string.IsNullOrEmpty(ItemID) ? "" : $@" and code.ItemID = '{ItemID}' ")}
                            {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and code.ParentAreaID = '{ParentAreaID}' ")}
                            {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID = '{ProID}' ")}
                            {(string.IsNullOrEmpty(CityID) ? "" : $@" and shop.AreaID = '{CityID}' ")}
                            {(string.IsNullOrEmpty(Keys)
                    ? ""
                    : $@" AND ( code.ShopName LIKE '%{Keys}%' OR shop.ShopCode LIKE '%{Keys}%' ) ")}
                   ) code ON table2.Yearv = code.Yearv
                             AND table2.Monthv = code.Monthv
                             AND table2.KindID = code.KindID
                             AND table2.SeriesID = code.SeriesID
                             AND table2.MaterialProperty = code.MaterialProperty
                             AND table2.ModelID = code.ModelID
                             AND table2.ItemID = code.ItemID
                             {con5}
GROUP BY table2.Yearv ,
        table2.Monthv ,
        table2.KindID ,
        table2.KindName ,
        table2.SeriesID ,
        table2.SeriesName ,
        table2.MaterialProperty ,
        table2.ModelID ,
        table2.ModelName ,
        table2.ItemID ,
        table2.ItemName ,
        table2.row ,
        code.Weekv ,
        code.OnlyCode ,
        code.ChooseFlag
        {con4}
ORDER BY table2.row ,
        code.Weekv desc,
        code.OnlyCode";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回企划唯一码报表总条数
        /// </summary>
        /// <param name="CodeOrShop"></param>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="ItemID"></param>
        /// <param name="ParentAreaID"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-05-04
        public int ReturnPLOnlyCodeCount(long CodeOrShop, string Yearv, string Monthv, string SeriesID,
            string MaterialProperty, string ModelID, string ItemID, string ParentAreaID, string ProID, string CityID,
            string Keys)
        {
            try
            {
                var con1 = @"";
                var con3 = @"";
                if (CodeOrShop == 1L)
                {
                    con1 = @" inner join ndw.ndw_global_dictionary dict on code.ParentAreaID=dict.dictName 
                            and  code.ExtrSource = dict.ExtrSource and code.CompanyID=dict.CompanyID 
                        inner join dw.dw_Shop shop on code.ShopID=shop.ShopID 
    and  code.ExtrSource = shop.ExtrSource and code.CompanyID=shop.CompanyID 
                        inner join ods.ods_erp_Bas_Area area on shop.AreaID=area.AreaID 
                            and  shop.ExtrSource = area.ExtrSource 
";
                    con3 = @" ,code.ParentAreaID,dict.dictValue,code.ShopID,code.ShopName ";
                    SeriesID = "";
                    MaterialProperty = "";
                    ModelID = "";
                    ItemID = "";
                }
                else
                {
                    ParentAreaID = "";
                    ProID = "";
                    CityID = "";
                    Keys = "";
                }
                var sql = $@"SELECT    count(1)
          FROM      ( SELECT    count(1)
                      FROM     ngoods.ngoods_PL_OnlyCode code
        INNER JOIN nods.nods_erp_SD_Mat_Kind kind ON code.KindID = kind.KindID  and  code.ExtrSource = kind.ExtrSource 
        INNER JOIN nods.nods_erp_SD_Mat_Series series ON code.SeriesID = series.SeriesID  and  code.ExtrSource = series.ExtrSource 
        INNER JOIN nods.nods_erp_SD_Mat_Model model ON code.ModelID = model.ModelID  and  code.ExtrSource = model.ExtrSource 
        INNER JOIN nods.nods_erp_SD_Mat_Item item ON code.ItemID = item.ItemID  and  code.ExtrSource = item.ExtrSource 
                                {con1}
                      WHERE     1=1
        and code.ExtrSource = '{UserInfo.UserExtrSource}' and code.CompanyID='{UserInfo.UserCompanyId}'
                                {(string.IsNullOrEmpty(Yearv) ? "" : $@" and code.Yearv = '{Yearv}' ")}
                                {(string.IsNullOrEmpty(Monthv) ? "" : $@" and code.Monthv = '{Monthv}' ")}
                                {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and code.SeriesID = '{SeriesID}' ")}
                                {(string.IsNullOrEmpty(MaterialProperty)
                    ? ""
                    : $@" and code.MaterialProperty = '{MaterialProperty}' ")}
                                {(string.IsNullOrEmpty(ModelID) ? "" : $@" and code.ModelID = '{ModelID}' ")}
                                {(string.IsNullOrEmpty(ItemID) ? "" : $@" and code.ItemID = '{ItemID}' ")}
                                {(string.IsNullOrEmpty(ParentAreaID)
                    ? ""
                    : $@" and code.ParentAreaID = '{ParentAreaID}' ")}
                                {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID = '{ProID}' ")}
                                {(string.IsNullOrEmpty(CityID) ? "" : $@" and shop.AreaID = '{CityID}' ")}
                                {(string.IsNullOrEmpty(Keys)
                    ? ""
                    : $@" AND ( code.ShopName LIKE '%{Keys}%' OR shop.ShopCode LIKE '%{Keys}%' ) ")}
                      GROUP BY  code.Yearv ,
                                code.Monthv ,
                                code.KindID ,
                                code.SeriesID ,
                                code.MaterialProperty ,
                                code.ModelID ,
                                code.ItemID 
                                {con3}
                    ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导出企划唯一码报表
        /// </summary>
        /// <param name="Keys"></param>
        /// <param name="CodeOrShop"></param>
        /// <returns></returns>
        public DataTable ExportPLOnlyCode(string Keys, long CodeOrShop)
        {
            try
            {
                var con1 = @"";
                var con2 = @"";
                var con3 = @"";
                if (CodeOrShop == 1L)
                {
                    con1 = @" inner join ndw.ndw_global_dictionary dict on code.ParentAreaID=dict.dictName 
                            and  code.ExtrSource = dict.ExtrSource and code.CompanyID=dict.CompanyID 
                        inner join ndw.ndw_Shop shop on code.ShopID=shop.ShopID 
                            and  code.ExtrSource = shop.ExtrSource and code.CompanyID=shop.CompanyID 
                        inner join nods.nods_erp_Bas_Area area on shop.AreaID=area.AreaID
                            and  shop.ExtrSource = area.ExtrSource 
                    ";
                    con2 = @" ,code.ParentAreaID,dict.dictValue as ParentAreaName,code.ShopID,code.ShopName ";
                    con3 = @" ,code.ParentAreaID,dict.dictValue,code.ShopID,code.ShopName ";
                }
                var sql = $@"SELECT  code.Yearv ,
        code.Monthv ,
        code.Weekv,
        code.KindID ,
        kind.KindName ,
        code.SeriesID ,
        series.SeriesName ,
        code.MaterialProperty ,
        code.ModelID ,
        model.ModelName ,
        code.ItemID ,
        item.ItemName ,
        code.OnlyCode ,
        sum(code.DeepQty) as DeepQty,
        CASE ISNULL(code.ChooseFlag, 0)
          WHEN 1 THEN 'Y'
          ELSE ''
        END AS ChooseFlag
        {con2}
FROM    ngoods.ngoods_PL_OnlyCode code
        INNER JOIN nods.nods_erp_SD_Mat_Kind kind ON code.KindID = kind.KindID  and  code.ExtrSource = kind.ExtrSource 
        INNER JOIN nods.nods_erp_SD_Mat_Series series ON code.SeriesID = series.SeriesID  and  code.ExtrSource = series.ExtrSource 
        INNER JOIN nods.nods_erp_SD_Mat_Model model ON code.ModelID = model.ModelID  and  code.ExtrSource = model.ExtrSource 
        INNER JOIN nods.nods_erp_SD_Mat_Item item ON code.ItemID = item.ItemID  and  code.ExtrSource = item.ExtrSource 
        {con1}
WHERE   1 = 1
      and   code.ExtrSource = '{UserInfo.UserExtrSource}' and code.CompanyID='{UserInfo.UserCompanyId}'
        {(string.IsNullOrEmpty(Keys) ? "" : Keys)}
GROUP BY code.Yearv ,
        code.Monthv ,
        code.Weekv,
        code.KindID ,
        kind.KindName ,
        code.SeriesID ,
        series.SeriesName ,
        code.MaterialProperty ,
        code.ModelID ,
        model.ModelName ,
        code.ItemID ,
        item.ItemName ,
        code.OnlyCode ,
        code.ChooseFlag
        {con3}
ORDER BY code.Yearv DESC ,
        code.Monthv DESC {con3},
        code.KindID ,
        code.SeriesID ,
        code.MaterialProperty ,
        code.ModelID ,
        code.ItemID,
        code.Weekv desc";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-05-05
        [MustLogin]
        [Description("创建唯一码或流行色")]
        [ReturnType(typeof(IList<PLOnlyCodeModel.ShowModel>))]
        public ApiResult CreatePLOnlyCode([ParamDescription("唯一码或流行色")] string CodeOrColor = "")
        {
            try
            {
                var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                if (set == null) return Error("没有要修改企划的月份");
                if (string.IsNullOrEmpty(CodeOrColor)) return Error("唯一码或流行色未选");
                if (CodeOrColor == "getOnlyCodeColor")
                {
                    var dt = ReturnPLOnlyCode(set.Yearv.Value, set.Monthv.Value, "Limit 1");
                    if (!(dt != null && dt.Rows.Count > 0))
                        return Error($"{set.Yearv.Value}年{set.Monthv.Value}月的唯一码还没生成!");
                    var count = BeforeCreateColor(set.Yearv.Value, set.Monthv.Value);
                    if (count > 0) return Error("流行色已分配完成！");
                }
                var server = IpAddressHelper.WebConfig("python-server"); //"192.168.10.127";
                var username = IpAddressHelper.WebConfig("python-server-user"); //"bi";
                var port = Convert.ToInt32(IpAddressHelper.WebConfig("python-server-port")); //"22";
                var password = IpAddressHelper.WebConfig("python-server-pass"); //"Km_etl2017";
                var command =
                        $"/usr/bin/python /home/bi/scripts/python/ngoods/plan/{CodeOrColor}.py {UserInfo.UserCompanyId} {set.Yearv.Value} {set.Monthv.Value}";
                    // /usr/bin/python 
                var result = ConnLinux(server, username, port, password, command);
                return result.Contains("success") ? GetPLOnlyCode() : Error(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-05-05
        [MustLogin]
        [Description("获取唯一码列表")]
        [ReturnType(typeof(IList<PLOnlyCodeModel.ShowModel>))]
        public ApiResult GetPLOnlyCode()
        {
            try
            {
                var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                if (set == null) return Error("没有要修改企划的月份");
                var dataTable = ReturnPLOnlyCode(set.Yearv.Value, set.Monthv.Value, null);
                var list = ModelConvertHelper<PLOnlyCodeModel.ShowModel>.ConvertToModel(dataTable).GroupBy(d => d.ItemID);
                var query = list.Select(d => new PLOnlyCodeModel.ShowModel()
                {
                    ItemID = d.Key,
                    Yearv = d.First().Yearv,
                    Monthv = d.First().Monthv,
                    ItemName = d.First().ItemName,
                    count = d.Count(),
                    list = d.ToList()
                }).ToList();
                return Success(query);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回唯一码列表
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-05-05
        public DataTable ReturnPLOnlyCode(long Yearv, long Monthv,string Keys)
        {
            try
            {
                var sql = $@"SELECT  code.Yearv ,
        code.Monthv ,
        code.Weekv ,
        code.OnlyCode ,
        code.ChooseFlag ,
        code.ItemID ,
        item.ItemName
FROM    ngoods.ngoods_PL_OnlyCode code
        INNER JOIN nods.nods_erp_SD_Mat_Item item ON code.ItemID = item.ItemID
        and code.ExtrSource = item.ExtrSource
        where  code.ExtrSource = '{UserInfo.UserExtrSource}' and code.CompanyID='{UserInfo.UserCompanyId}' and code.Yearv={Yearv} and code.Monthv={Monthv}
GROUP BY code.Yearv ,
        code.Monthv ,
        code.Weekv ,
        code.OnlyCode ,
        code.ChooseFlag ,
        code.ItemID ,
        item.ItemName
ORDER BY code.Yearv DESC ,
        code.Monthv DESC ,
        code.Weekv DESC ,
        code.OnlyCode ,
        code.ChooseFlag ,
        code.ItemID ,
        item.ItemName
{(string.IsNullOrEmpty(Keys) ? "" : Keys)}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 判断流行色是否已分配
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-05-05
        public long BeforeCreateColor(long Yearv,long Monthv)
        {
            try
            {
                var sql = $@"
   select count(1) from ngoods.ngoods_PL_OnlyCode where  ExtrSource = '{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' and Yearv={Yearv} and Monthv={Monthv}
   and (ChooseFlag=1 or CutFlag=1)";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt64(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
